package cn.edu.sdjzu.xg.eagle.dao;

import cn.edu.sdjzu.xg.eagle.Service.DepartmentService;
import cn.edu.sdjzu.xg.eagle.entity.Department;
import cn.edu.sdjzu.xg.eagle.entity.Pagination;
import cn.edu.sdjzu.xg.eagle.entity.Teacher;
import cn.edu.sdjzu.xg.eagle.exception.BysjException;
import cn.edu.sdjzu.xg.eagle.util.Condition;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class TeacherDao implements TeacherDaoInterface{
    private  static TeacherDao teacherDao = null;
    private TeacherDao(){}
    public static TeacherDao getInstance(){
        if(teacherDao == null){
            teacherDao = new TeacherDao();
        }
        return teacherDao;
    }
    @Override
    public Long add(Teacher teacher, Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement
                ("insert into teacher" + "(name,no)" + "values(?,?)", Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, teacher.getName());
        preparedStatement.setString(2,teacher.getNo());
        int affectRowNum = preparedStatement.executeUpdate();
        Long idOfAddedTeacher = 0L;
        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        if(resultSet.next()){
            idOfAddedTeacher = resultSet.getLong(1);
        }
        return idOfAddedTeacher;
    }

    @Override
    public boolean delete(Teacher teacher, Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("delete from teacher where id = ?");
        preparedStatement.setLong(1, teacher.getId());
        int affectRowNum = preparedStatement.executeUpdate();
        return affectRowNum > 0;
    }
    @Override
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList) throws SQLException , BysjException {
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();
        StringBuilder sql_select =new StringBuilder("select * from teacher");
        if(conditionList == null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"Teacher");
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while(resultSet_select.next()){
            Long id = resultSet_select.getLong("id");
            String name = resultSet_select.getString("name");
            Long departmentId = resultSet_select.getLong("department_id");
            Department department = DepartmentService.getInstance().find(departmentId);
            Teacher desiredTeacher = new Teacher(id,name,department);
            desiredTeachers.add(desiredTeacher);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredTeachers;
    }
    @Override
    public Collection<Teacher> findAll(List<Condition>conditionList, Pagination pagination) throws SQLException , BysjException{
        Connection connection = JdbcHelper.getConn();
        Collection<Teacher>desiredTeachers = new HashSet<>();
        StringBuilder sql_select =new StringBuilder("select * from teacher");
        if(conditionList == null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"Teacher",conditionList);
        String paginationClause  = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while(resultSet_select.next()){
            Long id = resultSet_select.getLong("id");
            String name = resultSet_select.getString("name");
            Long departmentId = resultSet_select.getLong("department_id");
            Department department = DepartmentService.getInstance().find(departmentId);
            Teacher desiredTeacher = new Teacher(id,name,department);
            desiredTeachers.add(desiredTeacher);
        }
        JdbcHelper.close(resultSet_select, pstmt_select,connection);
        return desiredTeachers;
    }
}
